package lanxing.chances.util.file.xls;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * @author <a href="mailto:lanxing@chances.com.cn">lanxing</a>
 * @version 2019年7月1日
 * @version 2019年7月20日
 *
 */
public class XlsUtils {

    public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

    public static void xls2xlsByWorkbook(String sourcePath, String destPath)
            throws BiffException, IOException, RowsExceededException, WriteException {
        Workbook wb = null;
        WritableWorkbook wwb = null;

        try {
            wb = Workbook.getWorkbook(new File(sourcePath));
            Sheet readSheet = wb.getSheet(0);

            wwb = Workbook.createWorkbook(new File(destPath));
            WritableSheet writeSheet = wwb.createSheet("shee_1", 0);

            int rows = readSheet.getRows();
            for (int i = 0; i < rows; i++) {

                Cell[] currentRowCellArray = readSheet.getRow(i);

                for (int j = 0; j < currentRowCellArray.length; j++) {
                    Label label = new Label(j, i, currentRowCellArray[j].getContents());
                    writeSheet.addCell(label);
                }
            }
            wwb.write();
        } finally {
            if (wwb != null) {
                wwb.close();
            }
            if (wb != null) {
                wb.close();
            }
        }
    }

    public static void xls2xlsByHSSFWorkbook(String sourcePath, String destPath)
            throws IOException, BiffException {
        @SuppressWarnings("resource")
        HSSFWorkbook hwSource = new HSSFWorkbook(new FileInputStream(new File(sourcePath)));
        HSSFSheet sheetSource = hwSource.getSheetAt(0);
        int rowAmountSource = sheetSource.getPhysicalNumberOfRows();

        @SuppressWarnings("resource")
        HSSFWorkbook hwDest = new HSSFWorkbook();
        HSSFSheet sheetDest = hwDest.createSheet("sheet0");
        for (int i = 0; i < rowAmountSource; i++) {
            System.out.println("第" + i + "行");
            HSSFRow rowSource = sheetSource.getRow(i);
            HSSFRow rowDest = sheetDest.createRow(i);
            for (int j = 0; j < rowSource.getLastCellNum(); j++) {
                HSSFCell cellSource = rowSource.getCell(j);
                if (cellSource == null) {
                    continue;
                }

                HSSFCell cellDest = rowDest.createCell(j);

                CellType cellType = cellSource.getCellType();
                switch (cellType) {
                case STRING:
                    cellDest.setCellValue(cellSource.getStringCellValue());
                    break;
                case BOOLEAN:
                    cellDest.setCellValue(cellSource.getBooleanCellValue());
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cellSource)) {
                        Date cellDate = cellSource.getDateCellValue();
                        cellDest.setCellValue(sdf.format(cellDate));
                    } else {
                        cellDest.setCellValue(cellSource.getNumericCellValue());
                    }

                default:
                    break;
                }
            }
        }
        hwDest.write(new FileOutputStream(destPath));
    }

    public static void xls2xlsByXSSFWorkbook(String sourcePath, String destPath)
            throws IOException, BiffException {
        @SuppressWarnings("resource")
        XSSFWorkbook xwSource = new XSSFWorkbook(new FileInputStream(new File(sourcePath)));
        XSSFSheet sheetSource = xwSource.getSheetAt(0);
        int rowAmountSource = sheetSource.getPhysicalNumberOfRows();

        @SuppressWarnings("resource")
        XSSFWorkbook hwDest = new XSSFWorkbook();
        XSSFSheet sheetDest = hwDest.createSheet("sheet0");
        for (int i = 0; i < rowAmountSource; i++) {
            System.out.println("第" + i + "行");
            XSSFRow rowSource = sheetSource.getRow(i);

            if (rowSource == null) {
                continue;
            }

            XSSFRow rowDest = sheetDest.createRow(i);
            short lastCellNum = rowSource.getLastCellNum();
            System.out.println("共有" + lastCellNum + "列");
            for (int j = 0; j < lastCellNum; j++) {
                XSSFCell cellSource = rowSource.getCell(j);
                if (cellSource == null) {
                    continue;
                }

                XSSFCell cellDest = rowDest.createCell(j);

                CellType cellType = cellSource.getCellType();
                switch (cellType) {
                case STRING:
                    cellDest.setCellValue(cellSource.getStringCellValue());
                    break;
                case BOOLEAN:
                    cellDest.setCellValue(cellSource.getBooleanCellValue());
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cellSource)) {
                        Date cellDate = cellSource.getDateCellValue();
                        cellDest.setCellValue(sdf.format(cellDate));
                    } else {
                        cellDest.setCellValue(cellSource.getNumericCellValue());
                    }

                default:
                    break;
                }
            }
        }
        hwDest.write(new FileOutputStream(destPath));
    }

    public static void main(String[] args)
            throws RowsExceededException, BiffException, WriteException, IOException {
        // String sourcePath = "E:\\Users\\blue
        // star\\git\\bobo_user_data_fake\\data\\bobo_users_login.xls";
        // String destPath = "E:\\Users\\blue
        // star\\git\\bobo_user_data_fake\\data\\bobo_users_login_util_1.xls";

        // xls2xlsByWorkbook(sourcePath, destPath);
        // xls2xlsByHSSFWorkbook(sourcePath, destPath);

        String sourcePath = "E:\\Users\\blue star\\git\\bobo_user_data_fake\\data\\bobo_users_login_util_2.xlsx";
        String destPath = "E:\\Users\\blue star\\git\\bobo_user_data_fake\\data\\bobo_users_login_util_2_copy.xlsx";
        xls2xlsByXSSFWorkbook(sourcePath, destPath);
    }
}
